package com.livableindex.repository;

import com.livableindex.entity.WeatherData;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.util.List;

@Repository
public interface WeatherDataRepository extends JpaRepository<WeatherData, Long> {
    
    List<WeatherData> findByProvinceAndCityAndDistrictOrderByDateAsc(
            String province, String city, String district);
    
    List<WeatherData> findByProvinceAndCityOrderByDateAsc(
            String province, String city);
    
    List<WeatherData> findByProvinceOrderByDateAsc(String province);
    
    List<WeatherData> findByDateBetweenOrderByDateAsc(LocalDate startDate, LocalDate endDate);
    
    @Query("SELECT DISTINCT w.province FROM WeatherData w ORDER BY w.province")
    List<String> findAllProvinces();
    
    @Query("SELECT DISTINCT w.city FROM WeatherData w WHERE w.province = :province ORDER BY w.city")
    List<String> findCitiesByProvince(@Param("province") String province);
    
    @Query("SELECT DISTINCT w.district FROM WeatherData w WHERE w.province = :province AND w.city = :city ORDER BY w.district")
    List<String> findDistrictsByProvinceAndCity(
            @Param("province") String province, @Param("city") String city);
    
    @Query("SELECT AVG(w.livableIndex) FROM WeatherData w WHERE w.province = :province AND w.city = :city AND w.district = :district")
    Double calculateAverageLivableIndex(
            @Param("province") String province, @Param("city") String city, @Param("district") String district);
    
    @Query("SELECT AVG(w.aqi) FROM WeatherData w WHERE w.province = :province AND w.city = :city AND w.district = :district")
    Double calculateAverageAqi(
            @Param("province") String province, @Param("city") String city, @Param("district") String district);
    
    @Query("SELECT AVG(w.temperature) FROM WeatherData w WHERE w.province = :province AND w.city = :city AND w.district = :district")
    Double calculateAverageTemperature(
            @Param("province") String province, @Param("city") String city, @Param("district") String district);
}
